package com.cntcdt.api.db.dao.base;

import java.util.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;

import com.coola.jutil.sql.DBEngine;
import com.coola.jutil.sql.DBFactory;
import com.coola.jutil.data.DataPage;
import com.coola.jutil.sql.ResultPage;
import com.ibm.icu.util.Calendar;

import javax.sql.rowset.CachedRowSet;

import com.cntcdt.api.db.model.GroupDeviceDayDivideCntModel;
import com.cntcdt.api.db.model.PartnerMoneyRecodeModel;

/**
 * 这是cdt_partner_money_recode的Dao实现类的基类
 */
public abstract class PartnerMoneyRecodeDaoBase {

	/**
	 * 日志处理类实例
	 */
	private static Log logger = LogFactory.getLog(PartnerMoneyRecodeDaoBase.class);
	/**
	 * 只读DB引擎
	 */
	private DBEngine readDBEngine = DBFactory.getDBEngine("main-statistic-b");
	/**
	 * 只写DB引擎
	 */
	private DBEngine writeDBEngine = DBFactory.getDBEngine("main-statistic");
	/**
	 * cdt_partner_money_recode表
	 */
	private static final String TABLE = "cdt_partner_money_recode";

	/**
	 * 默认构造方法
	 */
	public PartnerMoneyRecodeDaoBase() {
	}

	/**
	 * 插入一条数据
	 * 
	 * @param model
	 *            PartnerMoneyRecodeModel对象
	 * @return 返回插入影响行数
	 */
	public int insert(PartnerMoneyRecodeModel model) {
		DBEngine localEngine = DBFactory.getKeepConnDBEngine("main-statistic");
		String sql = "INSERT INTO " + TABLE + " (Money,UserId,AddTime,UpdateTime,Status,GroupId) VALUES (?,?,?,?,?,?)";
		Object[] paramObj = { model.getMoney(), model.getUserId(), model.getAddTime(), model.getUpdateTime(),
				model.getStatus(), model.getGroupId() };
		int id = 0;
		try {
			id = localEngine.executeUpdate(sql, paramObj);
			ResultSet rs = localEngine.executeQuery("select last_insert_id() as id");
			if (rs.next()) {
				id = rs.getInt("id");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			localEngine.close();
		}
		return id;
	}

	/**
	 * 修改一条数据
	 * 
	 * @param model
	 *            PartnerMoneyRecodeModel对象
	 * @return 修改成功返回true,否则返回false
	 */
	public boolean update(PartnerMoneyRecodeModel model) {
		String sql = "UPDATE  " + TABLE
				+ " SET Money = ? ,UserId = ? ,AddTime = ? ,UpdateTime = ? ,Status = ? ,GroupId = ? WHERE Id = ? ";
		Object[] paramObj = { model.getMoney(), model.getUserId(), model.getAddTime(), model.getUpdateTime(),
				model.getStatus(), model.getGroupId(), model.getId() };
		try {
			return writeDBEngine.executeUpdate(sql, paramObj) > 0;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 按主键删除一条数据
	 * 
	 * @param id
	 *            id
	 * @return 删除成功返回true,否则返回false
	 */
	public boolean deleteByPrimaryKey(Integer id) {
		String sql = "DELETE FROM " + TABLE + " WHERE id = ?";
		Object[] paramObjs = { id };
		try {
			return writeDBEngine.executeUpdate(sql, paramObjs) > 0;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 获得model集合
	 * 
	 * @param sql
	 *            查询语句
	 * @return 返回Model集合
	 */
	protected List<PartnerMoneyRecodeModel> queryModelList(String sql) {
		List<PartnerMoneyRecodeModel> modelList = new ArrayList<PartnerMoneyRecodeModel>();
		try {
			ResultSet rs = readDBEngine.executeQuery(sql);
			while (rs.next()) {
				modelList.add(new PartnerMoneyRecodeModel(rs));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return modelList;
	}

	/**
	 * 获得model集合
	 * 
	 * @param sql
	 *            查询语句
	 * @param objs
	 *            参数集
	 * @return 返回Model集合
	 */
	protected List<PartnerMoneyRecodeModel> queryModelList(String sql, Object[] paramObjs) {
		List<PartnerMoneyRecodeModel> modelList = new ArrayList<PartnerMoneyRecodeModel>();
		try {
			ResultSet rs = readDBEngine.executeQuery(sql, paramObjs);
			while (rs.next()) {
				modelList.add(new PartnerMoneyRecodeModel(rs));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return modelList;
	}

	/**
	 * 按主键查询model
	 * 
	 * @param id
	 *            id
	 * @return model
	 */
	public PartnerMoneyRecodeModel findByPrimaryKey(Integer id) {
		String sql = "SELECT * FROM " + TABLE + " WHERE id = ?";
		Object[] paramObjs = { id };
		List<PartnerMoneyRecodeModel> modelList = this.queryModelList(sql, paramObjs);
		if (modelList != null && modelList.size() > 0) {
			return modelList.get(0);
		}
		return null;
	}

	/**
	 * 获得model集合
	 * 
	 * @return 返回Model集合
	 */
	public List<PartnerMoneyRecodeModel> findAll() {
		String sql = "SELECT * FROM " + TABLE;
		return queryModelList(sql);
	}

	/**
	 * 获分页
	 * 
	 * @param sql
	 *            查询条件
	 * @param pageSize
	 *            分页大小
	 * @param pageNo
	 *            页码
	 * @return 返回分页对象
	 */
	protected DataPage<PartnerMoneyRecodeModel> findPage(String sql, int pageSize, int pageNo) {
		CachedRowSet rs = null;
		DataPage<PartnerMoneyRecodeModel> resultList = null;
		try {
			ResultPage page = readDBEngine.queryPage(sql, pageSize, pageNo);
			rs = page.getRecord();
			List<PartnerMoneyRecodeModel> list = new ArrayList<PartnerMoneyRecodeModel>();
			while (rs.next()) {
				list.add(new PartnerMoneyRecodeModel(rs));
			}
			if (list.size() > 0) {
				resultList = new DataPage<PartnerMoneyRecodeModel>(list, page.getTotalRecordCount(), pageSize, pageNo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return resultList;
	}

	/**
	 * 获分页
	 * 
	 * @param pageSize
	 *            分页大小
	 * @param pageNo
	 *            页码
	 * @return 返回分页对象
	 */
	public DataPage<PartnerMoneyRecodeModel> findPage(int pageSize, int pageNo) {
		String sql = "SELECT * FROM " + TABLE;
		return findPage(sql, pageSize, pageNo);
	}

	public List<PartnerMoneyRecodeModel> findByGroupIdSortTime(int groupId,int sortTime) {
		sortTime = sortTime*20;
		String sql = "SELECT * FROM " + TABLE + " WHERE groupId = ? order by updateTime DESC limit ?,20";
		Object[] paramObjs = { groupId,sortTime };
		List<PartnerMoneyRecodeModel> modelList = this.queryModelList(sql, paramObjs);
		return modelList;
	}
	/**
	 * 按商家id查询充值记录
	 * @param groupId
	 * @return
	 */
	public List<PartnerMoneyRecodeModel> findByGroupId(int groupId) {
		String sql = "SELECT * FROM " + TABLE + " WHERE groupId = ? order by updateTime DESC";
		Object[] paramObjs = { groupId };
		List<PartnerMoneyRecodeModel> modelList = this.queryModelList(sql, paramObjs);
		return modelList;
	}

	/**
	 * 查询商家当天充值总额
	 * @param groupId
	 * @return
	 */
	public PartnerMoneyRecodeModel findTodayMoneySum(int groupId) {
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.DATE, 0);
		String todayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
		String sql = "SELECT id,sum(money) as money,userId,addTime,updateTime,status,groupId FROM " + TABLE + " WHERE updateTime LIKE '"+todayDate+" %' AND groupId = ? AND status = 1 order by addTime";
		Object[] paramObjs = { groupId };
		List<PartnerMoneyRecodeModel> modelList = this.queryModelList(sql, paramObjs);
	       if(modelList.size() > 0){
	    	   return modelList.get(0);
	       }
	       return null;
	}
	
	/**
	 * 查询商家每月充值金额
	 * @param groupId
	 * @return
	 */
	public List<PartnerMoneyRecodeModel> findMonthMoneySum(int groupId,int sortTime){
		sortTime = sortTime*20;
        String sql = "select id,sum(money) as money,userId,addTime,updateTime,DATE_FORMAT(updateTime,'%Y-%m') months,status,groupId from "+TABLE+" WHERE groupId = ? AND status = 1 group by months order by months DESC limit ?,20";
        Object[] paramObjs = { groupId,sortTime };
        List<PartnerMoneyRecodeModel> modelList = this.queryModelList(sql, paramObjs);
	    return modelList;
	}
		
}
